from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from threading import Thread
from datetime import datetime, timedelta
from sql import MySQLDB
from myexcel import *
from mytools import *
from config import *
from class_shanbiao import TrademarkScraper
import tkinter.messagebox as messagebox
import sys,os,traceback
import time,threading,tkinter.font

if getattr(sys, 'frozen', False):
    base_path = os.path.join(sys._MEIPASS, "playwright/.local-browsers")
    os.environ["PLAYWRIGHT_BROWSERS_PATH"] = base_path
class UIFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        dbdiv=DBFrame(self)
        dbdiv.pack(pady=3,fill=X,padx=3)
        ZGSBWFrame(self,dbdiv).pack(pady=3,fill=X,padx=3)


class DBFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.login=False
        self.tables=[]
        self.createWidgets()
        Thread(target=self.getDB).start()

    def createWidgets(self):
        basic_info=get_allconfig()
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='数据库信息', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=X,padx=2,pady=2)
        Label(f00, text='服务器地址:').pack(side=LEFT, padx=2, pady=2)
        self.DBHostIpt = Entry(f00,width=10)
        self.DBHostIpt.pack(side=LEFT, padx=2, pady=2)
        self.DBHostIpt.insert(0,basic_info['DB_HOST'])



        Label(f00, text='数据库账户:').pack(side=LEFT, padx=2, pady=2)
        self.DBUidIpt = Entry(f00,width=5)
        self.DBUidIpt.pack(side=LEFT, padx=2, pady=2)
        self.DBUidIpt.insert(0,basic_info['DB_UID'])

        
        Label(f00, text='数据库密码:').pack(side=LEFT, padx=2, pady=2)
        self.DBPwdIpt = Entry(f00)
        self.DBPwdIpt.pack(side=LEFT, padx=2, pady=2)
        self.DBPwdIpt.insert(0,basic_info['DB_PWD'])
        
        Label(f00, text='服务器端口:').pack(side=LEFT, padx=2, pady=2)
        self.DBPortIpt = Entry(f00,width=5)
        self.DBPortIpt.pack(side=LEFT, padx=2, pady=2)
        self.DBPortIpt.insert(0,basic_info['DB_PORT'])
        

        f01=Frame(f1)
        f01.pack(fill=X,padx=2,pady=2)
        
        Label(f01, text='选择数据库:').pack(side=LEFT, padx=2, pady=2)
        self.DBSelect = ttk.Combobox(f01,state='readonly')
        self.DBSelect['value'] = ['未加载']
        self.DBSelect.bind("<<ComboboxSelected>>", lambda e:Thread(target=self.change_db).start())
        self.DBSelect.pack(side=LEFT, padx=2, pady=2)
        self.DBSelect.current(0)

        self.btn_conenct=Button(f01,text='连接接服务器',command=lambda:Thread(target=self.connect_server).start(),fg='#ffffff',bg='green')
        self.btn_conenct.pack(side=LEFT,padx=2, pady=2)


    def getDB(self):

        basic_info=get_allconfig()
        host=basic_info['DB_HOST']
        uid=basic_info['DB_UID']
        pwd=basic_info['DB_PWD']
        port=basic_info['DB_PORT']
        dbnames=list_databases(host,uid,pwd,port)
        
        if dbnames:
            dbnames_lower=[dbname.lower() for dbname in dbnames]
            self.DBSelect['value']=dbnames
            select_db_name=basic_info['DB_NAME']
            if select_db_name.lower() in dbnames_lower:
                select_ind=dbnames_lower.index(select_db_name.lower())
                self.DBSelect.current(select_ind)
                self.login=True
            else:
                self.DBSelect.current(0)
            
            cur_db_name=self.DBSelect.get()
            print(f'当前选择数据库:{cur_db_name}')
            try:
                db=MySQLDB(cur_db_name,uid,pwd,host,port)
                self.tables=db.get_all_tables()
                db.close_connection()
            except Exception as e:
                print(f'初始化链接数据库错误 => {e}')
                traceback.print_exc()
                
        
    def connect_server(self):

        self.btn_conenct
        host=self.DBHostIpt.get().strip()
        uid=self.DBUidIpt.get().strip()
        pwd=self.DBPwdIpt.get().strip()
        port=int(self.DBPortIpt.get().strip())
        basic_info=get_allconfig()
        if not(host and uid and pwd and port):
            messagebox.showerror('提示','请输入完整信息')

        self.btn_conenct.config(state=DISABLED)
        select_db_name=basic_info['DB_NAME']
        dbnames=list_databases(host,uid,pwd,port)
        
        has_exist_db=False
        if dbnames:
            dbnames_lower=[dbname.lower() for dbname in dbnames]
            if select_db_name.lower() in dbnames_lower:
                has_exist_db=True
        else:
            dbnames=[]
        if not has_exist_db:
            create_database(host,uid,pwd,select_db_name)
            dbnames=[select_db_name]+dbnames

        self.DBSelect['value']=dbnames
        self.DBSelect.current(0)
        cur_db_name=self.DBSelect.get()
        update_config({'DB_NAME':cur_db_name,'DB_UID':uid,'DB_PWD':pwd,'DB_HOST':host,'DB_PORT':port})
        print(f'服务器《{host}》成功链接')
        messagebox.showinfo(f'提示',f'服务器《{host}》成功链接')
        
        self.btn_conenct.config(state=NORMAL)

    def get_conn(self):
        for jjj in range(3):
            try:
                cur_db_name=self.DBSelect.get()
                if cur_db_name =='未加载':
                    basic_info=get_allconfig()

                    cur_db_name=basic_info['DB_NAME']
                    host=basic_info['DB_HOST']
                    uid=basic_info['DB_UID']
                    pwd=basic_info['DB_PWD']
                    port=basic_info['DB_PORT']
                    return MySQLDB(cur_db_name,uid,pwd,host,port)
                else:
                    host=self.DBHostIpt.get().strip()
                    uid=self.DBUidIpt.get().strip()
                    pwd=self.DBPwdIpt.get().strip()
                    port=int(self.DBPortIpt.get().strip())
                    return MySQLDB(cur_db_name,uid,pwd,host,port)
            except Exception as e:
                print(f'链接服务器错误 => {e},正在重试...')
    

    def change_db(self):
        print(f'正在切换数据库...')
        db=self.get_conn()
        cur_db_name=self.DBSelect.get()
        self.tables=db.get_all_tables()
        db.close_connection()
        print(f'成功切换到数据库:{cur_db_name}')
        update_config({'DB_NAME':cur_db_name})

class ZGSBWFrame(Frame):
    def __init__(self,master,dbfra:DBFrame):
        Frame.__init__(self,master)
        self.dbfra=dbfra
        self.session=requests.Session()
        self.createWidgets()
        self.rwname = '采集中国商标网'
    def createWidgets(self):
        basic_info=get_allconfig()
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='中国商标网(cas.sbj.cnipa.gov.cn)', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)
        label_font = tkinter.font.Font(family="微软雅黑", size=10, weight="bold")
        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)

        
        f00=Frame(f1)
        f00.pack(fill=X,padx=2,pady=2)
        
        Label(f00, text='公告期号数据库:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.Tbname1Ipt = Entry(f00)
        self.Tbname1Ipt.pack(side=LEFT, padx=2, pady=2)
        self.Tbname1Ipt.insert(0,basic_info['TABLE_ZGSBQS_PRO'])


        Label(f00, text='商标列表数据库:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.Tbname2Ipt = Entry(f00)
        self.Tbname2Ipt.pack(side=LEFT, padx=2, pady=2)
        self.Tbname2Ipt.insert(0,basic_info['TABLE_ZGSB_PRO'])
        
        f01=Frame(f1)
        f01.pack(fill=X,padx=2,pady=2)
        

        Label(f01, text='选择公告期号:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.GGqhIpt = Entry(f01,width=5)
        self.GGqhIpt.pack(side=LEFT, padx=2, pady=2)
        self.GGqhIpt.insert(0,basic_info['CJ_ZGSB_GONGAOQIHAO'])

        
        Label(f01, text='账号:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.ZHIpt = Entry(f01,width=10)
        self.ZHIpt.pack(side=LEFT, padx=2, pady=2)
        self.ZHIpt.insert(0,basic_info['CJ_ZGSB_USER'])

                
        Label(f01, text='密码:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.PWDIpt = Entry(f01,width=10)
        self.PWDIpt.pack(side=LEFT, padx=2, pady=2)
        self.PWDIpt.insert(0,basic_info['CJ_ZGSB_PWD'])

        Label(f01, text='线程数:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.XCSelect = ttk.Combobox(f01,state='readonly',width=3)
        self.XCSelect['value'] = [1,2,3,4,5]
        self.XCSelect.pack(side=LEFT, padx=2, pady=2)
        self.XCSelect.set(basic_info['CJ_ZGSB_XCS'])


        f02=Frame(f1)
        f02.pack(fill=X,padx=2,pady=2)
        Label(f02, text='循环采集:',font=label_font).pack(side=LEFT, padx=2)
        self.LoopSelect = ttk.Combobox(f02,state="readonly",width=6)
        self.LoopSelect['value']=['否','是']
        self.LoopSelect.pack(side=LEFT, padx=2)
        self.LoopSelect.current(basic_info['CJ_ZGSB_LOOP'])

        Label(f02, text='循环间隔:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.SleepHourIpt = Entry(f02,width=7)
        self.SleepHourIpt.pack(side=LEFT, padx=2, pady=2)
        self.SleepHourIpt.insert(0,basic_info['CJ_ZGSB_LOOP_SLEEP'])
        Label(f02, text='小时').pack(side=LEFT, padx=2)

        Label(f02, text='定时任务:',font=label_font).pack(side=LEFT, padx=2)
        self.TimingHourSelect = ttk.Combobox(f02,state="readonly",width=6)
        self.TimingHourSelect['value']=['无定时']+[str(j) for j in range(0,24)]
        self.TimingHourSelect.pack(side=LEFT, padx=2)
        self.TimingHourSelect.current(basic_info['CJ_ZGSB_TIMING_HOUR'])
        Label(f02, text='时').pack(side=LEFT, padx=2)

        self.TimingMinSelect = ttk.Combobox(f02,state="readonly",width=6)
        self.TimingMinSelect['value']=[str(j) for j in range(0,60)]
        self.TimingMinSelect.pack(side=LEFT, padx=2)
        self.TimingMinSelect.current(basic_info['CJ_ZGSB_TIMING_MINUTE'])
        Label(f02, text='分').pack(side=LEFT, padx=2)


        f03=Frame(f1)
        f03.pack(fill=X,padx=2,pady=2)
        self.btn_cj=Button(f03,text='开始采集',command=lambda:Thread(target=self.begin_cj).start(),fg='#ffffff',bg='green')
        self.btn_cj.pack(side=LEFT,padx=2, pady=2)

        self.btn_timing=Button(f03,text='定时启动',command=lambda:Thread(target=self.time_begin_cj).start(),fg='#ffffff',bg='green')
        self.btn_timing.pack(side=LEFT,padx=2, pady=2)

        self.btn_cancel_timing=Button(f03,text='取消定时',command=lambda:Thread(target=self.camcel_timing).start(),fg='#ffffff',bg='green')
        self.btn_cancel_timing.pack(side=LEFT,padx=2, pady=2)

        
        f04=Frame(f1)
        f04.pack(fill=X,padx=2,pady=2)

        self.btn_end_cj=Button(f04,text='停止采集',command=lambda: Thread(target=self.end_cj, daemon=True).start(),fg='#ffffff',bg='green')
        self.btn_end_cj.pack(side=LEFT,padx=2, pady=2)

    def time_begin_cj(self):
        rwname=self.rwname

        db = self.dbfra.get_conn()
        if not db:
            messagebox.showerror('提示','请正确连接数据库')
            return
        tb1=self.Tbname1Ipt.get().strip()
        tb2=self.Tbname2Ipt.get().strip()
        uid=self.ZHIpt.get().strip()
        pwd=self.PWDIpt.get().strip()
        ggqh=int(self.GGqhIpt.get().strip())

        update_config({
            'TABLE_ZGSBQS_PRO':tb1,
            'TABLE_ZGSB_PRO':tb2,
            'CJ_ZGSB_USER':uid,
            'CJ_ZGSB_PWD':pwd,
            'CJ_ZGSB_GONGAOQIHAO':int(ggqh),
        })

        if not tb1 or not tb2 or not tb2 or not uid or not pwd or not ggqh:
            messagebox.showinfo('提示','请先设置采集参数')
            return

        tining_hour=self.TimingHourSelect.get()
        if tining_hour=='无定时':
            messagebox.showinfo('提示','请先设置定时时间')
            return
        
        self.cancel=False
        self.btn_timing.config(state=DISABLED)
        timing_hour=self.TimingHourSelect.get()
        timing_min=self.TimingMinSelect.get()
        update_config({'CJ_ZGSB_TIMING_HOUR':int(timing_hour),'CJ_ZGSB_TIMING_MINUTE':int(timing_min)})
        while True:
            timing_hour=self.TimingHourSelect.get()
            timing_min=self.TimingMinSelect.get()
            now = datetime.now()
            cur_hour=now.hour
            cur_min=now.minute

            if self.cancel:
                messagebox.showinfo('提示','成功取消定时')
                self.btn_cancel_timing.config(state=NORMAL)
                break
            if cur_hour==int(timing_hour) and cur_min==int(timing_min):
                if self.btn_cj.cget('state')=='normal':
                    print(f'《{rwname}》,当前时间:{now},目标时间:{tining_hour}:{timing_min}:00,达到目标时间启动')
                    self.begin_cj()
                else:
                    print(f'《{rwname}》,采集任务正在运行,跳过定时')
                    time.sleep(19)
            else:
                print(f'《{rwname}》,当前时间:{now},目标时间:{tining_hour}:{timing_min}:00,未达到目标时间')
                time.sleep(19)
            
        self.btn_timing.config(state=NORMAL)
    
    def camcel_timing(self):
        self.btn_cancel_timing.config(state=DISABLED)
        self.cancel=True

    def begin_cj(self):
        rwname=self.rwname
        db = self.dbfra.get_conn()
        if not db:
            messagebox.showerror('提示','请正确连接数据库')
            return

        tb1=self.Tbname1Ipt.get().strip()
        tb2=self.Tbname2Ipt.get().strip()
        ggqh=int(self.GGqhIpt.get().strip())
        uid=self.ZHIpt.get().strip()
        pwd=self.PWDIpt.get().strip()
        xcs=int(self.XCSelect.get())
        sleep_hour=self.SleepHourIpt.get().strip()
        if not tb1 or not tb2 or not uid or not pwd or not ggqh:
            messagebox.showinfo('提示','请先设置采集参数')
            return
        is_loop=self.LoopSelect.current()
        update_config({
            'TABLE_ZGSBQS_PRO':tb1,
            'TABLE_ZGSB_PRO':tb2,
            'CJ_ZGSB_USER':uid,
            'CJ_ZGSB_PWD':pwd,
            'CJ_ZGSB_XCS':xcs,
            'CJ_ZGSB_GONGAOQIHAO':int(ggqh),
            'CJ_ZGSB_LOOP':is_loop,
            'CJ_ZGSB_LOOP_SLEEP':float(sleep_hour)
        })

        if not db.table_exists(tb1):
            db.create_table(tb1,ZGSB_QS_COL,[('anncIssue','page','page_size')],['state','isuse'])

        if not db.table_exists(tb2):
            db.create_table(tb2,ZGSB_COL,[('anncIssue','regNo')],['is_dc','is_on'])



        db.close_connection()
        self.btn_cj.config(state=DISABLED)
        self.btn_timing.config(state=DISABLED)
        loop_count=1
        while True:

            print(f"《{rwname}》,期号数据库:{tb1},商标数据库:{tb2},是否循环:{is_loop},第 {loop_count} 轮")
            try:
                db_list=[self.dbfra.get_conn() for _ in range(xcs)]
                
                s = TrademarkScraper(uid, pwd,ggqh,xcs,db_list=db_list)
                self.s=s
                # 开线程启动异步任务
                t0=threading.Thread(
                    target=s.start_yb_task,
                    daemon=True
                )
                t0.start()
                t0.join()
            except Exception as e:
                traceback.print_exc()
                print(f'《{rwname}》,出现错误 =>{e}')
            
            loop_count+=1
            
            if not is_loop:
                break
            else:
                print(f'《{rwname}》,第{loop_count}轮循环,等待{sleep_hour}小时,开启下一轮采集...')
                time.sleep(float(sleep_hour)*3600)

        messagebox.showinfo('提示',f'《{rwname}》,采集结束')
        self.btn_cj.config(state=NORMAL)
        self.btn_timing.config(state=NORMAL)

    def end_cj(self):
        self.s.stop_cj_plist=True

import tkinter
list_win = tkinter.Tk()
list_win.wm_geometry("800x650")
list_win.title('中国商标网采集')
UIFrame(list_win).pack(fill=X, padx=3, pady=3)
list_win.mainloop()